CREATE NONCLUSTERED INDEX IX_FileBlob_DataFileID
ON FileBlob(DataFileID ASC)
GO

ALTER TABLE EventData
DROP COLUMN FrequencyDomainData
GO

ALTER TABLE MetersToDataPush
ALTER COLUMN RemoteXDAName varchar(200) NOT NULL
GO

ALTER TABLE RemoteXDAInstance
ADD UserAccountID uniqueidentifier FOREIGN KEY  REFERENCES UserAccount(ID) NOT NULL
GO

ALTER TABLE EASExtension
ADD WebPage VARCHAR(MAX) CONSTRAINT DF_EASExtension_WebPage DEFAULT '' NOT NULL
GO

ALTER TABLE EASExtension
DROP CONSTRAINT DF_EASExtension_WebPage
GO


ALTER PROCEDURE [dbo].[GetPreviousAndNextEventIdsForMeterLocation]
    @EventID as INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startTime DATETIME2
    DECLARE @meterLocationID INT
    DECLARE @prevID INT
    DECLARE @nextID INT

    SELECT
        @startTime = Event.StartTime,
        @meterLocationID = Meter.MeterLocationID
    FROM
        Event JOIN
        Meter ON Event.MeterID = Meter.ID
    WHERE Event.ID = @EventID

    SELECT @prevID = COALESCE
    (
        (
            SELECT MAX(Event.ID)
            FROM
                Event JOIN
                Meter ON Event.MeterID = Meter.ID
            WHERE
                Event.ID < @EventID AND
                StartTime = @startTime AND
                MeterLocationID = @meterLocationID
        ),
        (
            SELECT MAX(Event.ID)
            FROM
                Event JOIN
                Meter ON Event.MeterID = Meter.ID
            WHERE
                StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND MeterLocationID = @meterLocationID) AND
                MeterLocationID = @meterLocationID
        )
    )

    SELECT @nextID = COALESCE
    (
        (
            SELECT MIN(Event.ID)
            FROM
                Event JOIN
                Meter ON Event.MeterID = Meter.ID
            WHERE
                Event.ID > @EventID AND
                StartTime = @startTime AND
                MeterLocationID = @meterLocationID
        ),
        (
            SELECT MIN(Event.ID)
            FROM
                Event JOIN
                Meter ON Event.MeterID = Meter.ID
            WHERE
                StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND MeterLocationID = @meterLocationID) AND
                MeterLocationID = @meterLocationID
        )
    )

    SELECT
        @prevID AS previd,
        @nextID AS nextid
END
GO

ALTER PROCEDURE [dbo].[GetPreviousAndNextEventIdsForMeter]
    @EventID as INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startTime DATETIME2
    DECLARE @meterID INT
    DECLARE @prevID INT
    DECLARE @nextID INT

    SELECT
        @startTime = Event.StartTime,
        @meterID = Event.MeterID
    FROM Event
    WHERE Event.ID = @EventID

    SELECT @prevID = COALESCE
    (
        (
            SELECT MAX(Event.ID)
            FROM Event
            WHERE
                Event.ID < @EventID AND
                StartTime = @startTime AND
                MeterID = @meterID
        ),
        (
            SELECT MAX(Event.ID)
            FROM Event
            WHERE
                StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND MeterID = @meterID) AND
                MeterID = @meterID
        )
    )

    SELECT @nextID = COALESCE
    (
        (
            SELECT MIN(Event.ID)
            FROM Event
            WHERE
                Event.ID > @EventID AND
                StartTime = @startTime AND
                MeterID = @meterID
        ),
        (
            SELECT MIN(Event.ID)
            FROM Event
            WHERE
                StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND MeterID = @meterID) AND
                MeterID = @meterID
        )
    )

    SELECT
        @prevID AS previd,
        @nextID AS nextid
END
GO

ALTER PROCEDURE [dbo].[GetPreviousAndNextEventIdsForLine]
    @EventID as INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startTime DATETIME2
    DECLARE @lineID INT
    DECLARE @prevID INT
    DECLARE @nextID INT

    SELECT
        @startTime = Event.StartTime,
        @lineID = Event.LineID
    FROM Event
    WHERE Event.ID = @EventID

    SELECT @prevID = COALESCE
    (
        (
            SELECT MAX(Event.ID)
            FROM Event
            WHERE
                Event.ID < @EventID AND
                StartTime = @startTime AND
                LineID = @lineID
        ),
        (
            SELECT MAX(Event.ID)
            FROM Event
            WHERE
                StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND LineID = @lineID) AND
                LineID = @lineID
        )
    )

    SELECT @nextID = COALESCE
    (
        (
            SELECT MIN(Event.ID)
            FROM Event
            WHERE
                Event.ID > @EventID AND
                StartTime = @startTime AND
                LineID = @lineID
        ),
        (
            SELECT MIN(Event.ID)
            FROM Event
            WHERE
                StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND LineID = @lineID) AND
                LineID = @lineID
        )
    )

    SELECT
        @prevID AS previd,
        @nextID AS nextid
END
GO

ALTER PROCEDURE [dbo].[selectCompletenessForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT  Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%'
FROM
    (
        SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount
        FROM
        (
            SELECT Date,
                    CASE
                        WHEN Completeness > 100.0 THEN 'First'
                        WHEN 98.0 <= Completeness AND Completeness <= 100.0 THEN 'Second'
                        WHEN 90.0 <= Completeness AND Completeness < 98.0 THEN 'Third'
                        WHEN 70.0 <= Completeness AND Completeness < 90.0 THEN 'Fourth'
                        WHEN 50.0 <= Completeness AND Completeness < 70.0 THEN 'Fifth'
                        WHEN 0.0 < Completeness AND Completeness < 50.0 THEN 'Sixth'
                    END AS CompletenessLevel
            FROM
            (
                SELECT Date, 100.0 * CAST(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints AS FLOAT) / CAST(NULLIF(ExpectedPoints, 0) AS FLOAT) AS Completeness
                FROM MeterDataQualitySummary
                WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, ',')) AND MeterID IN (SELECT * FROM authMeters(@username))
            ) MeterDataQualitySummary
        ) MeterDataQualitySummary
        GROUP BY Date, CompletenessLevel
) MeterDataQualitySummary
PIVOT
(
    SUM(MeterDataQualitySummary.MeterCount)
    FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth)
) as pvt
ORDER BY Date

END
GO

ALTER PROCEDURE [dbo].[selectCorrectnessForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT  Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%'
FROM
    (
        SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount
        FROM
        (
            SELECT Date,
                    CASE
                        WHEN Correctness > 100.0 THEN 'First'
                        WHEN 98.0 <= Correctness AND Correctness <= 100.0 THEN 'Second'
                        WHEN 90.0 <= Correctness AND Correctness < 98.0 THEN 'Third'
                        WHEN 70.0 <= Correctness AND Correctness < 90.0 THEN 'Fourth'
                        WHEN 50.0 <= Correctness AND Correctness < 70.0 THEN 'Fifth'
                        WHEN 0.0 < Correctness AND Correctness < 50.0 THEN 'Sixth'
                    END AS CompletenessLevel
            FROM
            (
                SELECT Date, 100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT) AS Correctness
                FROM MeterDataQualitySummary
                WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, ',')) AND MeterID IN (SELECT * FROM authMeters(@username))
            ) MeterDataQualitySummary
        ) MeterDataQualitySummary
        GROUP BY Date, CompletenessLevel
) MeterDataQualitySummary
PIVOT
(
    SUM(MeterDataQualitySummary.MeterCount)
    FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth)
) as pvt
ORDER BY Date

END
GO

ALTER PROCEDURE [dbo].[selectFaultsForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, Event.StartTime), DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, Event.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, Event.StartTime), DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)'
END



DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SET @SQLStatement =
' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) +
' FROM ( ' +
'       SELECT ' + @dateStatement + ' AS Date, Line.VoltageKV, COUNT(*) AS thecount ' +
'       FROM Event JOIN '+
'       EventType ON Event.EventTypeID = EventType.ID JOIN ' +
'       Line ON Event.LineID = Line.ID ' +
'       WHERE EventType.Name = ''Fault'' AND ' +
'       MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '','')) AND Event.StartTime >= @startDate AND Event.StartTime < @endDate  ' +
'       GROUP BY ' + @groupByStatement + ', EventType.Name, Line.VoltageKV ' +
'       ) as eventtable ' +
' PIVOT( ' +
'       SUM(eventtable.thecount) ' +
'       FOR eventtable.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ' +
' ) as pvt ' +
' ORDER BY Date '

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom
END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsCorrectness]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
    DECLARE @endDate DATE = CAST(@EventDateTo AS DATE)

    SELECT 
        Meter.ID, 
        Meter.Name, 
        MeterLocation.Longitude, 
        MeterLocation.Latitude, 
		COALESCE(CAST(CAST(SUM(MeterDataQualitySummary.GoodPoints) AS FLOAT) / NULLIF(CAST(SUM(MeterDataQualitySummary.ExpectedPoints) AS FLOAT), 0) * 100 AS INT),0) AS Count,
		COALESCE(SUM(MeterDataQualitySummary.ExpectedPoints) , 0) AS ExpectedPoints,
		COALESCE(SUM(MeterDataQualitySummary.GoodPoints),0) AS GoodPoints,
		COALESCE(SUM(MeterDataQualitySummary.LatchedPoints),0) AS LatchedPoints,
		COALESCE(SUM(MeterDataQualitySummary.UnreasonablePoints),0) AS UnreasonablePoints,
		COALESCE(SUM(MeterDataQualitySummary.NoncongruentPoints),0) AS NoncongruentPoints,
		COALESCE(SUM(MeterDataQualitySummary.DuplicatePoints),0) AS DuplicatePoints
        FROM
            Meter JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT JOIN
			( SELECT * FROM MeterDataQualitySummary WHERE [Date] BETWEEN @startDate AND @endDate) as MeterDataQualitySummary ON MeterDataQualitySummary.MeterID = Meter.ID
		WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ',')) 
		GROUP BY Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude
        ORDER BY Meter.Name
END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsFaults]
    @EventDateFrom DateTime,
    @EventDateTo DateTime,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
    SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) + '
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t


SET @SQLStatement =
' SELECT Meter.ID,
         Meter.Name,
         MeterLocation.Longitude,
         MeterLocation.Latitude,
         ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
         ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
    (
        SELECT MeterID,
               COUNT(*) AS EventCount,
               VoltageKV
        FROM Event JOIN
             EventType ON Event.EventTypeID = EventType.ID JOIN
             Line ON Event.LineID = Line.ID
        WHERE
             StartTime >= @startDate AND StartTime < @endDate AND
             EventType.Name = ''Fault''
        GROUP BY Event.MeterID, VoltageKV
       ) as ed
       PIVOT(
             SUM(ed.EventCount)
             FOR ed.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
       ) as pvt On pvt.MeterID = meter.ID
    WHERE
        Meter.ID in (select * from authMeters(@username)) AND
        Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterIds,  '',''))

ORDER BY Meter.Name'

print @SQLStatement
exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END
GO

ALTER PROCEDURE [dbo].[selectSiteLinesDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @startDate DATETIME = @EventDate 
	DECLARE @endDate DATETIME

    IF @context = '180d'
    BEGIN
		SET @startDate = DATEADD(HOUR, -180, @EventDate)
		SET @endDate = @EventDate
    END

	IF @context = '90d'
    BEGIN
		SET @startDate = DATEADD(DAY, -90, @EventDate)
		SET @endDate = @EventDate
    END

    IF @context = '30d'
    BEGIN
		SET @startDate = DATEADD(DAY, -30, @EventDate)
		SET @endDate = @EventDate
    END

    IF @context = '7d'
    BEGIN
		SET @startDate = DATEADD(DAY, -7, @EventDate)
		SET @endDate = @EventDate
    END

	IF @context = '24h'
	BEGIN
		SET @startDate = DATEADD(HOUR, -24, @EventDate)
		SET @endDate = @EventDate
	END

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END


	DECLARE @simStartDate DATETIME = DATEADD(SECOND, -5, @startDate)
	DECLARE @simEndDate DATETIME = DATEADD(SECOND, 5, @endDate)
	print @simStartDate
	print @simEndDate
    DECLARE @localEventDate DATE = CAST(@EventDate AS DATE)
    DECLARE @localMeterID INT = CAST(@MeterID AS INT)
	DECLARE @timeWindow int = (SELECT Value FROM DashSettings WHERE Name = 'System.TimeWindow')

    ; WITH cte AS
    (
        SELECT
            Event.LineID AS thelineid, 
            Event.ID AS theeventid, 
            EventType.Name AS theeventtype,
            CAST(Event.StartTime AS VARCHAR(26)) AS theinceptiontime,
            MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename,
            Line.VoltageKV AS voltage,
            COALESCE(FaultSummary.FaultType, Phase.Name, '') AS thefaulttype,
            CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE COALESCE(CAST(CAST(FaultSummary.Distance AS DECIMAL(16, 4)) AS NVARCHAR(19)), '') END AS thecurrentdistance,
            dbo.EventHasImpactedComponents(Event.ID) AS pqiexists,
            Event.StartTime,
            CASE EventType.Name
                WHEN 'Sag' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Interruption' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Swell' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude DESC, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Fault' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY IsSelectedAlgorithm DESC, IsSuppressed, IsValid DESC, Inception)
                ELSE ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Event.ID)
            END AS RowPriority,
			(SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.StartTime BETWEEN DateAdd(SECOND, -5, Event.StartTime) and  DateAdd(SECOND, 5, Event.StartTime)) as SimultaneousCount,
			(SELECT COUNT(*) FROM Event as EventCount WHERE EventTypeID IN (SELECT ID FROM EventType WHERE Name = 'Sag' OR Name = 'Fault') AND EventCount.StartTime BETWEEN DateAdd(SECOND, -@timeWindow, Event.StartTime) and  DateAdd(SECOND, @timeWindow, Event.StartTime)) as SimultaneousFAndSCount,
			(SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.LineID = Event.LineID AND EventCount.StartTime BETWEEN DateAdd(Day, -60, Event.StartTime) and  Event.StartTime) as SixtyDayCount,
			Event.UpdatedBy,
			(SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as Note
        FROM
            Event JOIN
            EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN
            Disturbance ON Disturbance.EventID = Event.ID LEFT OUTER JOIN
            FaultSummary ON FaultSummary.EventID = Event.ID  LEFT OUTER JOIN
            Phase ON Disturbance.PhaseID = Phase.ID JOIN
            Meter ON Meter.ID = @MeterID JOIN
            Line ON Event.LineID = Line.ID JOIN
            MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID
        WHERE
            Event.StartTime >= @startDate AND Event.StartTime < @endDate AND 
            Event.MeterID = @localMeterID AND
            (Phase.ID IS NULL OR Phase.Name <> 'Worst')
    )
    SELECT
        thelineid,
        theeventid,
        theeventtype,
        theinceptiontime,
        thelinename,
        voltage,
        thefaulttype,
        thecurrentdistance,
        pqiexists,
		SimultaneousCount,
		SimultaneousFAndSCount,
		SixtyDayCount,
		UpdatedBy,
		Note
    INTO #temp
    FROM cte
    WHERE RowPriority = 1
    ORDER BY StartTime

    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql = COALESCE(@sql + ',dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName, 'dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName)
    FROM EASExtension

	DECLARE @serviceList NVARCHAR(MAX)
	SELECT @serviceList = COALESCE(@serviceList + ',' + ServiceName, ServiceName)
	FROM EASExtension
	Set @serviceList = '''' + @serviceList + ''''
	
	
    SET @sql = COALESCE('SELECT *,' + @sql + ', '+ @ServiceList +'as ServiceList FROM #temp', 'SELECT *, '''' AS ServiceList FROM #temp')
    print @sql
	EXEC sp_executesql @sql

    DROP TABLE #temp
	
END
GO

ALTER PROCEDURE [dbo].[selectSiteLinesDisturbanceDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @worstPhaseID INT = (SELECT ID FROM Phase WHERE Name = 'Worst')
	DECLARE @startDate DATETIME = @EventDate 
	DECLARE @endDate DATETIME


    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

	DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

	SELECT 
		Event.LineID AS thelineid, 
		Event.ID AS theeventid, 
		Disturbance.ID as disturbanceid,
		EventType.Name AS disturbancetype,
		Phase.Name AS phase,
        CASE Disturbance.PerUnitMagnitude
            WHEN -1E308 THEN 'NaN'
            ELSE CAST(Disturbance.PerUnitMagnitude AS VARCHAR(8))
        END AS magnitude,
        CASE Disturbance.DurationSeconds
            WHEN -1E308 THEN 'NaN'
            ELSE CAST(CONVERT(DECIMAL(10,3), Disturbance.DurationSeconds) AS VARCHAR(14))
        END AS duration,
		CAST(Disturbance.StartTime AS VARCHAR(26)) AS theinceptiontime,
        dbo.DateDiffTicks('1970-01-01', Disturbance.StartTime) / 10000.0 AS startmillis,
        dbo.DateDiffTicks('1970-01-01', Disturbance.EndTime) / 10000.0 AS endmillis,
		DisturbanceSeverity.SeverityCode,
		MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename,
		Line.VoltageKV AS voltage,
	    (SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as notes
	FROM
		Event JOIN
		Disturbance ON Disturbance.EventID = Event.ID JOIN
        Disturbance WorstDisturbance ON
            Disturbance.EventID = WorstDisturbance.EventID AND
            Disturbance.PerUnitMagnitude = WorstDisturbance.PerUnitMagnitude AND
            Disturbance.DurationSeconds = WorstDisturbance.DurationSeconds JOIN
		EventType ON Disturbance.EventTypeID = EventType.ID JOIN
		Phase ON Disturbance.PhaseID = Phase.ID JOIN
		DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
		Meter ON Meter.ID = @MeterID JOIN
		Line ON Event.LineID = Line.ID JOIN
		MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID JOIN
		VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID	
	WHERE
		Event.StartTime >= @startDate AND Event.StartTime < @endDate AND 
		Event.MeterID = @MeterID AND
        WorstDisturbance.PhaseID = @worstPhaseID AND
        Disturbance.PhaseID <> @worstPhaseID AND
		VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC')
	ORDER BY
		Event.StartTime ASC

END
GO

ALTER PROCEDURE [dbo].[selectSitesBreakersDetailsByDate]
    @EventDate AS DATETIME,
    @MeterID AS NVARCHAR(MAX),
    @username AS NVARCHAR(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime
    DECLARE @endDate DateTime

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

    SELECT
        Meter.ID AS meterid,
        Event.ID AS theeventid,
        EventType.Name AS eventtype,
        BreakerOperation.ID AS breakeroperationid,
        CAST(CAST(BreakerOperation.TripCoilEnergized AS TIME) AS NVARCHAR(100)) AS energized,
        BreakerOperation.BreakerNumber AS breakernumber,
        MeterLine.LineName AS linename,
        Phase.Name AS phasename,
        CAST(BreakerOperation.BreakerTiming AS DECIMAL(16,5)) AS timing,
        CAST(BreakerOperation.StatusTiming AS DECIMAL(16,5)) AS statustiming,
        BreakerOperation.BreakerSpeed AS speed,
        BreakerOperation.StatusBitChatter AS chatter,
        BreakerOperation.DcOffsetDetected AS dcoffset,
        BreakerOperationType.Name AS operationtype,
		(SELECT COUNT(*) FROM EventNote WHERE EventNote.EventID = Event.ID) as notecount
    FROM
        BreakerOperation JOIN
        Event ON BreakerOperation.EventID = Event.ID JOIN
        EventType ON EventType.ID = Event.EventTypeID JOIN
        Meter ON Meter.ID = Event.MeterID JOIN
        Line ON Line.ID = Event.LineID JOIN
        MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN
        BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN
        Phase ON BreakerOperation.PhaseID = Phase.ID
    WHERE
        TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate AND
        Meter.ID IN (SELECT * FROM  dbo.String_to_int_table(@MeterID, ',')) AND
        Meter.ID IN (select * from authMeters(@username))
END
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('COMTRADEUseRelaxedValidation', 'False', 'False')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('DataPusher.OnlyValidFaults', 'True', 'True')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('DataPusher.TimeWindow', '72', '72')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FileWatcherEnabled', 'True', 'True')
GO

UPDATE Setting SET Name = 'DataPusher.Enabled' WHERE Name = 'EnableDataPusher'
GO

UPDATE Setting SET Name = 'PQMarkAggregation.Enable' WHERE Name = 'EnablePQMarkAggregator'
GO

UPDATE Setting SET Name = 'PQMarkAggregation.Frequency' WHERE Name = 'PQMarkAggregationFrequency'
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('System.TimeWindow', '1', 1)
GO

UPDATE DashSettings SET Name = 'System.XDAInstance' WHERE Name = 'XDAInstance'
GO